<?php


$dbname = 'open_scriptures';
$dbuser = 'root';
$dbpass = '';
$dbhost = "localhost";

$sql =
<<<SQL
		SELECT
			c.TABLE_NAME,
			c.COLUMN_NAME,
			c.IS_NULLABLE,
			c.EXTRA,
			c.DATA_TYPE,
			c.CHARACTER_MAXIMUM_LENGTH,
			/*c.CHARACTER_OCTET_LENGTH,*/
			c.COLUMN_DEFAULT,
			c.COLUMN_KEY,
		
			kcu.CONSTRAINT_NAME,
			kcu.REFERENCED_TABLE_NAME,
			kcu.REFERENCED_COLUMN_NAME,
			
			tc.CONSTRAINT_TYPE,
			c.COLUMN_COMMENT
		FROM
			COLUMNS c
			LEFT JOIN (
				KEY_COLUMN_USAGE kcu,
				TABLE_CONSTRAINTS tc
			)
			ON (
				kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_SCHEMA  = c.TABLE_SCHEMA AND
				kcu.CONSTRAINT_NAME   = tc.CONSTRAINT_NAME AND
				kcu.TABLE_NAME        = tc.TABLE_NAME AND
				c.TABLE_NAME          = kcu.TABLE_NAME AND
				c.COLUMN_NAME         = kcu.COLUMN_NAME
			)
		WHERE
			c.TABLE_SCHEMA = "$dbname"
		ORDER BY
			c.TABLE_NAME,
			c.ORDINAL_POSITION;
SQL;
	
	//Connect to the database
	$link = @mysql_connect($dbhost, $dbuser, $dbpass);
	if(!$link)
		throw new Exception(mysql_error(), mysql_errno());
	@mysql_select_db('INFORMATION_SCHEMA');
	$introspect = @mysql_query($sql);
	
	
	
	$tables = array();
	while($r = mysql_fetch_object($introspect)){
		//Setup this table
		if(!isset($tables[$r->TABLE_NAME])){
			$tables[$r->TABLE_NAME] = array();
			$tables[$r->TABLE_NAME]['columns'] = array();
		}
		
		//Set up new column
		if(empty($tables[$r->TABLE_NAME]['columns'][$r->COLUMN_NAME])){
			$tables[$r->TABLE_NAME]['columns'][$r->COLUMN_NAME] = array();
			$tables[$r->TABLE_NAME]['columns'][$r->COLUMN_NAME]['constraints'] = array();
		}
		
		//Copy column properties
		$c = &$tables[$r->TABLE_NAME]['columns'][$r->COLUMN_NAME];
		$c['DATA_TYPE'] = $r->DATA_TYPE;
		$c['IS_NULLABLE'] = $r->IS_NULLABLE == 'NO' ? false : true;
		if(!empty($r->CHARACTER_MAXIMUM_LENGTH))
			$c['CHARACTER_MAXIMUM_LENGTH'] = (int)$r->CHARACTER_MAXIMUM_LENGTH;
		$c['COLUMN_DEFAULT'] = $r->COLUMN_DEFAULT;
		if(!empty($r->COLUMN_KEY))
			$c['COLUMN_KEY'] = $r->COLUMN_KEY;
		if(!empty($r->COLUMN_COMMENT))
			$c['COLUMN_COMMENT'] = $r->COLUMN_COMMENT;
		
		//Add constraint details
		if(!empty($r->CONSTRAINT_NAME) || !empty($r->REFERENCED_TABLE_NAME) || !empty($r->REFERENCED_COLUMN_NAME) || !empty($r->CONSTRAINT_TYPE)){
			$constraint = array();
			
			#if(!empty($r->CONSTRAINT_NAME))
			#	$constraint['CONSTRAINT_NAME'] = $r->CONSTRAINT_NAME;
			if(!empty($r->REFERENCED_TABLE_NAME))
				$constraint['REFERENCED_TABLE_NAME'] = $r->REFERENCED_TABLE_NAME;
			if(!empty($r->REFERENCED_COLUMN_NAME))
				$constraint['REFERENCED_COLUMN_NAME'] = $r->REFERENCED_COLUMN_NAME;
			if(!empty($r->CONSTRAINT_TYPE))
				$constraint['CONSTRAINT_TYPE'] = $r->CONSTRAINT_TYPE;
			
			//array_push($c['constraints'], $constraint);
			$c['constraints'][$r->CONSTRAINT_NAME] = $constraint;
		}
		
		#$c['dependencies'] = array();
	}
	#file_put_contents('schema-data.txt', var_export($tables, true));
	
	#Iterate over each table and build dependency lists
	
	//$dependencies = array();
	
	
	//function getAllDeps($table){
	//	global $tables, $dependencies;
	//	if(!empty($dependencies[$table]))
	//		return $dependencies[$table];
	//	
	//	$dependency = array($table);
	//	foreach($tables[$table]['columns'] as $column_name => $column){
	//		
	//		if(!empty($column['constraints'])){
	//			foreach($column['constraints'] as $constraint){
	//				if($constraint['REFERENCED_TABLE_NAME']){
	//					#print_r($constraint);
	//					#$dependency[] = $constraint['REFERENCED_TABLE_NAME'] . ":$column_name";
	//					#$dependency = array_merge($dependency, getAllDeps($constraint['REFERENCED_TABLE_NAME']));
	//					echo $constraint['REFERENCED_TABLE_NAME'];
	//					
	//				}
	//			}
	//		}
	//		
	//		
	//	}
	//	return $dependency;
	//}
	$dependencies = array();
	foreach($tables as $table_name => &$data){
		$dependencies[$table_name] = array($table_name, array());
	}
	
	
	foreach($tables as $table_name => &$data){
		#print "<h3>$table_name</h3>";
		foreach($data['columns'] as $column_name => $column){
			if(!empty($column['constraints'])){
				foreach($column['constraints'] as $constraint){
					if($constraint['CONSTRAINT_TYPE'] == 'PRIMARY KEY'){
						$dependencies[$table_name][1][] = $column_name;
					}
					
					
					if($constraint['REFERENCED_TABLE_NAME']){
						if($constraint['REFERENCED_TABLE_NAME'] != 'text'){
							$dependencies[$table_name][2][$column_name] = &$dependencies[$constraint['REFERENCED_TABLE_NAME']];
						}
						else {
							$dependencies[$table_name][2][$column_name] = array($constraint['REFERENCED_TABLE_NAME']);
						}
					}
				}
			}
		}
	}
	
	
	print "<pre>";
	foreach($dependencies as $table){
		$uri = "/$table[0]/{" . join('}:{', $table[1]) . "}/";
		$uri = str_replace('_', '-', $uri);
		print "$uri\n";
	}
	print "</pre>";
	
	
	
	echo '<hr>';
		echo "<pre>";
	print_r($dependencies);
		echo "</pre>";
	
	foreach($tables as $table_name => $data){
		print "<h3>$table_name</h3>";
		#$table_nicename = orm_phpize($table_name);
		#$table = preg_replace_callback('/(^\w)|(_\w)/', create_function('$match', 'return ltrim(strtoupper($match[0]), "_"); '), $table);
		
		$schema = str_replace("\n", "\r\n", ltrim(var_export($data['columns'], true)));
		$schema = str_replace("  ", "\t", $schema);
		$schema = preg_replace("{(?<=\r\n)}", "\t", $schema);
		
		$primaryKeys = array();
		foreach($data['columns'] as $name => $attrs){
			$isPrimary = false;
			foreach($attrs['constraints'] as $constraint){
				if($constraint['CONSTRAINT_TYPE'] == 'PRIMARY KEY'){
					//$isPrimary = true;
					$primaryKeys[] = preg_replace('{:.+}', '', $name); //TODO
				}
			}
		}
		
		$selectArgList = array();
		#foreach($primaryKeys as $pk){
		#	$selectArgList[] = "\$$pk";
		#}
		$selectArgList = join(', ', array_map(create_function('$a', 'return "\$$a";'), $primaryKeys));
		$selectByArgList = "\r\n" . join(', ', array_map(create_function('$a', "return \"\t\t\t'\$a' => \$\$a\r\n\";"), $primaryKeys)) . "\t\t";
		
		
		//NOTE: We can simply pass the arguments AND the $schema to the parent method which can then do the data-validation and all of
		//      the complicated logic. Remember also that we need to plug-in custom validation constraints, filters, and
		//      visibility constraints from the user.
		
		
		echo '<pre>';
		print_r($data);
		echo '</pre>';
		
	}
?>